
DROP TABLE IF EXISTS jms_dwm.dwm_overall_target_network_sum_day_dt;

create external table jms_dwm.dwm_overall_target_network_sum_day_dt
(
stat_date string comment '统计日期',
network_type string comment '网点类型',
network_code string comment '网点编码',
network_name string comment '网点名称',
franchisee_code string comment '加盟商编码',
franchisee_name string comment '加盟商名称',
agent_code string comment '代理区编码',
agent_name string comment '代理区名称',
virt_code string comment '虚拟代理区code',
virt_name string comment '虚拟代理区name',
kpi_code string comment '指标code',
kpi_name string comment '指标名称',
kpi_type string comment '指标类型,1-量值,2-率值',
kpi_fz decimal(18,6) comment '指标分子',
kpi_fm decimal(18,6) comment '指标分母',
kpi_values decimal(18,6) comment '指标值',
kpi_param int comment '指标参数(百分之,百万分之，万分之,1/100/10000)',
agent_principal_name  string comment '代理区负责人'
)
COMMENT '指标库应用网点维度日表'
PARTITIONED BY (dt string comment '分区日期')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dwm.db/external/dwm_overall_target_network_sum_day_dt'
TBLPROPERTIES (
    'discover.partitions' = 'false',
    'parquet.column.index.access' = 'true'
    );


---hive

ALTER TABLE jms_dwm.dwm_overall_target_network_sum_day_dt add COLUMNS(
  franchisee_principal_name  string COMMENT '加盟商负责人',
  PRINCIPAL_NAME  string COMMENT '负责人'
)

---oracle
alter table DWM_OVERALL_TARGET_NETWORK_SUM_DAY_DT add(
	FRANCHISEE_PRINCIPAL_NAME NVARCHAR2(200),
	PRINCIPAL_NAME NVARCHAR2(200)
	);

COMMENT ON COLUMN DWM_OVERALL_TARGET_NETWORK_SUM_DAY_DT.FRANCHISEE_PRINCIPAL_NAME IS '加盟商负责人';
COMMENT ON COLUMN DWM_OVERALL_TARGET_NETWORK_SUM_DAY_DT.PRINCIPAL_NAME IS '负责人';

